package com.huatai.vba.dal.dbf;

import java.sql.*;
import java.util.List;
import java.util.Map;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

public class DBFConnectUtils {

	private static final String baseUrl = "jdbc:odbc:driver={Microsoft Visual FoxPro Driver};SourceType=DBF;";

	private static final String baseUrl1 = "jdbc:odbc:dBASE Files;SourceTypde=dbf;";

	public static Connection getConnection(String path) throws SQLException {
		Connection con = null;
		try {
			String url = baseUrl + "SourceDB=" + path;

			con = DriverManager.getConnection(url);
		} catch (Exception e) {
			throw e;
		}

		return con;
	}

	public static Statement getStatement(Connection con) throws Exception {
		return con.createStatement();
	}

	public static ResultSet getResultSet(Statement st, String sql) throws Exception {
		return st.executeQuery(sql);
	}

	public static void CloseAll(Connection con, Statement st, ResultSet rs) throws Exception {
		try {
			if (rs != null) {
				rs.close();
			}
			if (st != null) {
				st.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			throw e;
		}
	}

	public static List<Map<String, Object>> executeQuery(String path, String sql) throws Exception {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		List<Map<String, Object>> list = null;
		try {
			con = getConnection(path);
			st = getStatement(con);
			rs = getResultSet(st, sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();
			String[] name = new String[count];
			for (int i = 0; i < count; i++)
				name[i] = rsmd.getColumnName(i + 1);
			list = Lists.newArrayList();
			Map<String, Object> map = Maps.newHashMap();
			while (rs.next()) {
				for (int i = 0; i < count; i++) {
					map.put(name[i], rs.getObject(name[i]));
				}
				list.add(map);
			}
		} catch (Exception ex) {
			throw ex;
		} finally {
			CloseAll(con, st, rs);
		}

		return list;
	}

	public static void main(String[] args) throws Exception {

		List list = DBFConnectUtils.executeQuery("c:/", "select *　from hk_zqbdjs946");
		System.out.println(list);
	}
}
